import 'dart:convert';
import 'package:sqflite/sqflite.dart';
import 'package:logger/logger.dart';
import '../core/database_helper.dart';
import '../core/app_config.dart';

/// 统计分析服务
class StatisticsService {
  static final _logger = Logger();
  final DatabaseHelper _dbHelper = DatabaseHelper();

  /// 获取总体统计信息
  Future<Map<String, dynamic>> getOverallStatistics() async {
    try {
      _logger.d('开始获取总体统计信息');
      final db = await _dbHelper.database;
      
      // 基础统计
      final basicStats = await db.rawQuery('''
        SELECT 
          COUNT(*) as total_records,
          COALESCE(SUM(production_capacity), 0) as total_production,
          COALESCE(SUM(energy_consumption), 0) as total_energy_consumption,
          COALESCE(SUM(carbon_emission), 0) as total_carbon_emission
        FROM carbon_footprint_records 
        WHERE is_deleted = 0
      ''');
      
      // 本月统计
      final currentMonth = DateTime.now();
      final monthStart = DateTime(currentMonth.year, currentMonth.month, 1);
      final monthEnd = DateTime(currentMonth.year, currentMonth.month + 1, 0);
      
      final monthlyStats = await db.rawQuery('''
        SELECT 
          COUNT(*) as monthly_records,
          COALESCE(SUM(production_capacity), 0) as monthly_production,
          COALESCE(SUM(carbon_emission), 0) as monthly_carbon_emission
        FROM carbon_footprint_records 
        WHERE is_deleted = 0 
          AND record_date >= ? 
          AND record_date <= ?
      ''', [monthStart.millisecondsSinceEpoch, monthEnd.millisecondsSinceEpoch]);
      
      // 工艺类型统计
      final processStats = await db.rawQuery('''
        SELECT 
          pt.type_name as process_type,
          COUNT(*) as record_count,
          COALESCE(SUM(cfr.production_capacity), 0) as total_production,
          COALESCE(SUM(cfr.carbon_emission), 0) as total_carbon_emission,
          COALESCE(AVG(CASE WHEN cfr.production_capacity > 0 THEN cfr.carbon_emission / cfr.production_capacity ELSE 0 END), 0) as avg_emission_intensity
        FROM carbon_footprint_records cfr
        LEFT JOIN process_types pt ON cfr.process_type = pt.id
        WHERE cfr.is_deleted = 0
        GROUP BY pt.type_name
        ORDER BY total_carbon_emission DESC
      ''');
      
      final result = {
        'basic_stats': {
          'total_records': basicStats.first['total_records'] ?? 0,
          'total_production': ((basicStats.first['total_production'] ?? 0) as num).toDouble(),
          'total_energy_consumption': ((basicStats.first['total_energy_consumption'] ?? 0) as num).toDouble(),
          'total_carbon_emission': ((basicStats.first['total_carbon_emission'] ?? 0) as num).toDouble(),
        },
        'monthly_stats': {
          'monthly_records': monthlyStats.first['monthly_records'] ?? 0,
          'monthly_production': ((monthlyStats.first['monthly_production'] ?? 0) as num).toDouble(),
          'monthly_carbon_emission': ((monthlyStats.first['monthly_carbon_emission'] ?? 0) as num).toDouble(),
        },
        'process_stats': processStats.map((row) => {
          'process_type': (row['process_type'] as String?) ?? '未知工艺',
          'record_count': (row['record_count'] as int?) ?? 0,
          'total_production': ((row['total_production'] ?? 0) as num).toDouble(),
          'total_carbon_emission': ((row['total_carbon_emission'] ?? 0) as num).toDouble(),
          'avg_emission_intensity': ((row['avg_emission_intensity'] ?? 0) as num).toDouble(),
        }).toList(),
      };
      
      _logger.i('总体统计信息获取成功: ${(result['basic_stats'] as Map<String, dynamic>)['total_records']} 条记录');
      return result;
    } catch (e, stackTrace) {
      _logger.e('获取总体统计信息失败', error: e, stackTrace: stackTrace);
      rethrow;
    }
  }

  /// 获取趋势分析数据
  Future<Map<String, dynamic>> getTrendAnalysis({
    required String timeUnit, // 'day', 'month', 'year'
    required int limit, // 限制返回的数据点数量
  }) async {
    try {
      _logger.d('开始获取趋势分析数据: $timeUnit, 限制: $limit');
      final db = await _dbHelper.database;
      
      String dateFormat;
      String groupBy;
      
      switch (timeUnit) {
        case 'day':
          dateFormat = "strftime('%Y-%m-%d', datetime(record_date/1000, 'unixepoch'))";
          groupBy = "strftime('%Y-%m-%d', datetime(record_date/1000, 'unixepoch'))";
          break;
        case 'month':
          dateFormat = "strftime('%Y-%m', datetime(record_date/1000, 'unixepoch'))";
          groupBy = "strftime('%Y-%m', datetime(record_date/1000, 'unixepoch'))";
          break;
        case 'year':
          dateFormat = "strftime('%Y', datetime(record_date/1000, 'unixepoch'))";
          groupBy = "strftime('%Y', datetime(record_date/1000, 'unixepoch'))";
          break;
        default:
          throw ArgumentError('不支持的时间单位: $timeUnit');
      }
      
      final trendData = await db.rawQuery('''
        SELECT 
          $dateFormat as time_period,
          COUNT(*) as record_count,
          COALESCE(SUM(production_capacity), 0) as total_production,
          COALESCE(SUM(energy_consumption), 0) as total_energy_consumption,
          COALESCE(SUM(carbon_emission), 0) as total_carbon_emission,
          COALESCE(AVG(CASE WHEN production_capacity > 0 THEN carbon_emission / production_capacity ELSE 0 END), 0) as avg_emission_intensity,
          COALESCE(AVG(CASE WHEN production_capacity > 0 THEN energy_consumption / production_capacity ELSE 0 END), 0) as avg_energy_intensity
        FROM carbon_footprint_records 
        WHERE is_deleted = 0
        GROUP BY $groupBy
        ORDER BY time_period DESC
        LIMIT ?
      ''', [limit]);
      
      final result = {
        'time_unit': timeUnit,
        'limit': limit,
        'trend_data': trendData.map((row) => {
          'time_period': row['time_period'],
          'record_count': (row['record_count'] as int?) ?? 0,
          'total_production': ((row['total_production'] ?? 0) as num).toDouble(),
          'total_energy_consumption': ((row['total_energy_consumption'] ?? 0) as num).toDouble(),
          'total_carbon_emission': ((row['total_carbon_emission'] ?? 0) as num).toDouble(),
          'avg_emission_intensity': ((row['avg_emission_intensity'] ?? 0) as num).toDouble(),
          'avg_energy_intensity': ((row['avg_energy_intensity'] ?? 0) as num).toDouble(),
        }).toList(),
      };
      
      _logger.i('趋势分析数据获取成功: ${(result['trend_data'] as List).length} 个数据点');
      return result;
    } catch (e, stackTrace) {
      _logger.e('获取趋势分析数据失败', error: e, stackTrace: stackTrace);
      rethrow;
    }
  }

  /// 获取对比分析数据
  Future<Map<String, dynamic>> getComparisonAnalysis({
    required String comparisonType, // 'process', 'time', 'region'
    Map<String, dynamic>? filters,
  }) async {
    try {
      _logger.d('开始获取对比分析数据: $comparisonType');
      final db = await _dbHelper.database;
      
      String groupBy;
      String selectFields;
      
      switch (comparisonType) {
        case 'process':
          groupBy = 'pt.type_name';
          selectFields = 'pt.type_name as comparison_key, pt.type_name as comparison_label';
          break;
        case 'time':
          groupBy = "strftime('%Y-%m', datetime(record_date/1000, 'unixepoch'))";
          selectFields = "strftime('%Y-%m', datetime(record_date/1000, 'unixepoch')) as comparison_key, strftime('%Y年%m月', datetime(record_date/1000, 'unixepoch')) as comparison_label";
          break;
        case 'region':
          groupBy = 'COALESCE(cfr.location, "未知地区")';
          selectFields = 'COALESCE(cfr.location, "未知地区") as comparison_key, COALESCE(cfr.location, "未知地区") as comparison_label';
          break;
        default:
          throw ArgumentError('不支持的对比类型: $comparisonType');
      }
      
      String whereClause = 'WHERE cfr.is_deleted = 0';
      List<dynamic> whereArgs = [];
      
      // 应用过滤器
      if (filters != null) {
        if (filters['start_date'] != null) {
          whereClause += ' AND cfr.record_date >= ?';
          whereArgs.add(filters['start_date']);
        }
        if (filters['end_date'] != null) {
          whereClause += ' AND cfr.record_date <= ?';
          whereArgs.add(filters['end_date']);
        }
        if (filters['process_type'] != null) {
          whereClause += ' AND cfr.process_type = ?';
          whereArgs.add(filters['process_type']);
        }
      }
      
      String fromClause;
      if (comparisonType == 'process') {
        fromClause = 'FROM carbon_footprint_records cfr LEFT JOIN process_types pt ON cfr.process_type = pt.id';
      } else {
        fromClause = 'FROM carbon_footprint_records cfr';
      }
      
      final comparisonData = await db.rawQuery('''
        SELECT 
          $selectFields,
          COUNT(*) as record_count,
          COALESCE(SUM(cfr.production_capacity), 0) as total_production,
          COALESCE(SUM(cfr.energy_consumption), 0) as total_energy_consumption,
          COALESCE(SUM(cfr.carbon_emission), 0) as total_carbon_emission,
          COALESCE(AVG(CASE WHEN cfr.production_capacity > 0 THEN cfr.carbon_emission / cfr.production_capacity ELSE 0 END), 0) as avg_emission_intensity,
          COALESCE(AVG(CASE WHEN cfr.production_capacity > 0 THEN cfr.energy_consumption / cfr.production_capacity ELSE 0 END), 0) as avg_energy_intensity
        $fromClause
        $whereClause
        GROUP BY $groupBy
        ORDER BY total_carbon_emission DESC
      ''', whereArgs);
      
      final result = {
        'comparison_type': comparisonType,
        'comparison_data': comparisonData.map((row) => {
          'comparison_key': row['comparison_key'],
          'comparison_label': row['comparison_label'],
          'record_count': (row['record_count'] as int?) ?? 0,
          'total_production': ((row['total_production'] ?? 0) as num).toDouble(),
          'total_energy_consumption': ((row['total_energy_consumption'] ?? 0) as num).toDouble(),
          'total_carbon_emission': ((row['total_carbon_emission'] ?? 0) as num).toDouble(),
          'avg_emission_intensity': ((row['avg_emission_intensity'] ?? 0) as num).toDouble(),
          'avg_energy_intensity': ((row['avg_energy_intensity'] ?? 0) as num).toDouble(),
        }).toList(),
      };
      
      _logger.i('对比分析数据获取成功: ${(result['comparison_data'] as List).length} 个对比项');
      return result;
    } catch (e, stackTrace) {
      _logger.e('获取对比分析数据失败', error: e, stackTrace: stackTrace);
      rethrow;
    }
  }

  /// 导出统计报表
  Future<String> exportStatisticsReport({
    required String reportType, // 'overall', 'trend', 'comparison'
    Map<String, dynamic>? parameters,
  }) async {
    try {
      _logger.d('开始导出统计报表: $reportType');
      
      Map<String, dynamic> data;
      switch (reportType) {
        case 'overall':
          data = await getOverallStatistics();
          break;
        case 'trend':
          final timeUnit = parameters?['time_unit'] ?? 'month';
          final limit = parameters?['limit'] ?? 12;
          data = await getTrendAnalysis(timeUnit: timeUnit, limit: limit);
          break;
        case 'comparison':
          final comparisonType = parameters?['comparison_type'] ?? 'process';
          final filters = parameters?['filters'];
          data = await getComparisonAnalysis(comparisonType: comparisonType, filters: filters);
          break;
        case 'comprehensive':
          // 综合报表包含所有类型的数据
          final overallData = await getOverallStatistics();
          final trendData = await getTrendAnalysis(timeUnit: 'month', limit: 12);
          final comparisonData = await getComparisonAnalysis(comparisonType: 'process');
          data = {
            'overall': overallData,
            'trend': trendData,
            'comparison': comparisonData,
          };
          break;
        default:
          throw ArgumentError('不支持的报表类型: $reportType');
      }
      
      // 生成CSV格式的报表
      final csvContent = _generateCSVReport(data, reportType);
      
      _logger.i('统计报表导出成功: $reportType');
      return csvContent;
    } catch (e, stackTrace) {
      _logger.e('导出统计报表失败', error: e, stackTrace: stackTrace);
      rethrow;
    }
  }

  /// 生成CSV格式报表
  String _generateCSVReport(Map<String, dynamic> data, String reportType) {
    final buffer = StringBuffer();
    
    switch (reportType) {
      case 'overall':
        buffer.writeln('总体统计报表');
        buffer.writeln('生成时间,${DateTime.now().toString()}');
        buffer.writeln();
        
        // 基础统计
        final basicStats = data['basic_stats'] as Map<String, dynamic>;
        buffer.writeln('基础统计');
        buffer.writeln('指标,数值');
        buffer.writeln('总记录数,${basicStats['total_records']}');
        buffer.writeln('总产能(吨),${basicStats['total_production']}');
        buffer.writeln('总能耗(kWh),${basicStats['total_energy_consumption']}');
        buffer.writeln('总碳排放(吨),${basicStats['total_carbon_emission']}');
        buffer.writeln();
        
        // 本月统计
        final monthlyStats = data['monthly_stats'] as Map<String, dynamic>;
        buffer.writeln('本月统计');
        buffer.writeln('指标,数值');
        buffer.writeln('本月记录数,${monthlyStats['monthly_records']}');
        buffer.writeln('本月产能(吨),${monthlyStats['monthly_production']}');
        buffer.writeln('本月碳排放(吨),${monthlyStats['monthly_carbon_emission']}');
        buffer.writeln();
        
        // 工艺类型统计
        final processStats = data['process_stats'] as List<dynamic>;
        buffer.writeln('工艺类型统计');
        buffer.writeln('工艺类型,记录数,总产能(吨),总碳排放(吨),平均排放强度(吨CO2/吨)');
        for (final process in processStats) {
          buffer.writeln('${process['process_type']},${(process['record_count'] as int?)?.toString() ?? '0'},${process['total_production']},${process['total_carbon_emission']},${process['avg_emission_intensity']}');
        }
        break;
        
      case 'trend':
        buffer.writeln('趋势分析报表');
        buffer.writeln('生成时间,${DateTime.now().toString()}');
        buffer.writeln('时间单位,${data['time_unit']}');
        buffer.writeln();
        
        final trendData = data['trend_data'] as List<dynamic>;
        buffer.writeln('时间周期,记录数,总产能(吨),总能耗(kWh),总碳排放(吨),平均排放强度(吨CO2/吨),平均能源强度(kWh/吨)');
        for (final trend in trendData) {
          buffer.writeln('${trend['time_period']},${(trend['record_count'] as int?)?.toString() ?? '0'},${trend['total_production']},${trend['total_energy_consumption']},${trend['total_carbon_emission']},${trend['avg_emission_intensity']},${trend['avg_energy_intensity']}');
        }
        break;
        
      case 'comparison':
        buffer.writeln('对比分析报表');
        buffer.writeln('生成时间,${DateTime.now().toString()}');
        buffer.writeln('对比类型,${data['comparison_type']}');
        buffer.writeln();
        
        final comparisonData = data['comparison_data'] as List<dynamic>;
        buffer.writeln('对比项,记录数,总产能(吨),总能耗(kWh),总碳排放(吨),平均排放强度(吨CO2/吨),平均能源强度(kWh/吨)');
        for (final comparison in comparisonData) {
          buffer.writeln('${comparison['comparison_label']},${(comparison['record_count'] as int?)?.toString() ?? '0'},${comparison['total_production']},${comparison['total_energy_consumption']},${comparison['total_carbon_emission']},${comparison['avg_emission_intensity']},${comparison['avg_energy_intensity']}');
        }
        break;
        
      case 'comprehensive':
        buffer.writeln('综合统计报表');
        buffer.writeln('生成时间,${DateTime.now().toString()}');
        buffer.writeln();
        
        // 总体统计部分
        final overallData = data['overall'] as Map<String, dynamic>;
        final basicStats = overallData['basic_stats'] as Map<String, dynamic>;
        buffer.writeln('总体统计');
        buffer.writeln('指标,数值');
        buffer.writeln('总记录数,${basicStats['total_records']}');
        buffer.writeln('总产能(吨),${basicStats['total_production']}');
        buffer.writeln('总能耗(kWh),${basicStats['total_energy_consumption']}');
        buffer.writeln('总碳排放(吨),${basicStats['total_carbon_emission']}');
        buffer.writeln();
        
        // 趋势分析部分
        final trendData = data['trend'] as Map<String, dynamic>;
        buffer.writeln('趋势分析');
        buffer.writeln('时间周期,记录数,总产能(吨),总能耗(kWh),总碳排放(吨)');
        final trendList = trendData['trend_data'] as List<dynamic>;
        for (final trend in trendList) {
          buffer.writeln('${trend['time_period']},${(trend['record_count'] as int?)?.toString() ?? '0'},${trend['total_production']},${trend['total_energy_consumption']},${trend['total_carbon_emission']}');
        }
        buffer.writeln();
        
        // 对比分析部分
        final comparisonData = data['comparison'] as Map<String, dynamic>;
        buffer.writeln('对比分析');
        buffer.writeln('对比项,记录数,总产能(吨),总能耗(kWh),总碳排放(吨)');
        final comparisonList = comparisonData['comparison_data'] as List<dynamic>;
        for (final comparison in comparisonList) {
          buffer.writeln('${comparison['comparison_label']},${(comparison['record_count'] as int?)?.toString() ?? '0'},${comparison['total_production']},${comparison['total_energy_consumption']},${comparison['total_carbon_emission']}');
        }
        break;
    }
    
    return buffer.toString();
  }
} 